import sys
import os
import datetime
import csv
import numpy

from PySide2 import QtCore
from PySide2.QtCore import Signal, Slot, QObject
from PySide2.QtCore import QPointF, QPoint, QSettings
from PySide2.QtGui import QKeySequence
from PySide2.QtWidgets import QFileDialog
from PySide2.QtWidgets import QWidget, QApplication, QMainWindow
from PySide2.QtWidgets import QHBoxLayout, QVBoxLayout
from PySide2.QtWidgets import QGroupBox, QAction
from PySide2.QtWidgets import QPushButton, QToolButton, QLineEdit
from PySide2.QtWidgets import QSpinBox, QCheckBox, QLabel
from PySide2.QtWidgets import QMessageBox
from PySide2.QtCharts import QtCharts
QChart = QtCharts.QChart
QChartView = QtCharts.QChartView
QLineSeries = QtCharts.QLineSeries
QValueAxis = QtCharts.QValueAxis

from matplotlib.backends.qt_compat import is_pyqt5
if is_pyqt5():
    from matplotlib.backends.backend_qt5agg import (
        FigureCanvas, NavigationToolbar2QT as NavigationToolbar)
else:
    from matplotlib.backends.backend_qt4agg import (
        FigureCanvas, NavigationToolbar2QT as NavigationToolbar)
from matplotlib.figure import Figure

from driver_mysql import MySQL
from draw_chart import DrawChart

from wwaiting import WWaiting
from flowlayout import FlowLayout

import constant

class MainWidgetSignals(QObject):
    connected = Signal()
    disconnected = Signal()
    moveStarted = Signal(int)
    moveCompleted = Signal()

class MainWidget(QWidget):
    signals = MainWidgetSignals()
    def __init__(self, parent=None):
        super(MainWidget, self).__init__(parent)
        self.setUi()
        self.configUi()

        self._mysql_colnames_x = None
        self._mysql_colnames_y = None
        def _set_colnames():
            self._mysql_colnames = None
            if self._ui_ckb_mysql_use_x.isChecked():
                if self._mysql_colnames_y and self._mysql_colnames_x:
                    self._mysql_colnames = self._mysql_colnames_y + self._mysql_colnames_x
                else:
                    self._mysql_colnames = self._mysql_colnames_y or self._mysql_colnames_x
            else:
                if self._mysql_colnames_y:
                    self._mysql_colnames = self._mysql_colnames_y[:]        
        def _set_colnames_x():
            try:
                self._mysql_colnames_x = eval(self._ui_le_mysql_colname_x.text())
            except:
                pass
        _set_colnames_x()
        self._ui_le_mysql_colname_x.editingFinished.connect(_set_colnames_x)
        self._ui_le_mysql_colname_x.editingFinished.connect(_set_colnames)
        def _set_colnames_y(): 
            try:
                self._mysql_colnames_y = eval(self._ui_le_mysql_colname.text())
            except:
                pass
        _set_colnames_y()
        self._ui_le_mysql_colname.editingFinished.connect(_set_colnames_y)
        self._ui_le_mysql_colname.editingFinished.connect(_set_colnames)
        _set_colnames()
        self._ui_ckb_mysql_use_x.clicked.connect(_set_colnames)

        self._ui_pb_mysql_connect.clicked[bool].connect(self._connect)
        self._ui_pb_move_pre.clicked.connect(self._move_pre)
        self._ui_pb_move_pre.clicked.connect(lambda : self.setEnabled(False))
        self._ui_pb_move_next.clicked.connect(self._move_next)
        self._ui_pb_move_next.clicked.connect(lambda : self.setEnabled(False))

        self._ui_pb_move_pre.clicked.connect(lambda : self.signals.moveStarted.emit(5000))
        self._ui_pb_move_next.clicked.connect(lambda : self.signals.moveStarted.emit(5000))

        self._ui_pb_move_pre.clicked.connect(lambda : self._ui_lbl_startdate.setEnabled(False))
        self._ui_pb_move_next.clicked.connect(lambda : self._ui_lbl_startdate.setEnabled(False))

        # self._ui_pb_move_pre.clicked.connect(lambda : self._dynamic_ax.clear())
        # self._ui_pb_move_next.clicked.connect(lambda : self._dynamic_ax.clear())

        self._ui_tbt_export.clicked.connect(self.save_current)
        self._ui_tbt_export.clicked.connect(lambda : self.setEnabled(False))

        self._ui_tbt_saveimage.clicked.connect(self.save_image)
        self._ui_tbt_printWindow.clicked.connect(self.print_window)

        self._mysql = MySQL()
        self._mysql.signals.connectFinished.connect(lambda :self._ui_pb_mysql_connect.setEnabled(True))
        self._mysql.signals.getPageStarted.connect(self.display_init)
        self._mysql.signals.getPageSplited.connect(self.display_page)
        self._is_draw_legend = True
        def addLegend():
            if self._is_draw_legend:
                self._ui_chart_view.figure.legend(loc=1, ncol=3, bbox_transform=self._ui_chart_axis[0].transAxes)
                self._is_draw_legend = False
        def resetDraw(): self._is_draw_legend = True
        self._mysql.signals.getPageStarted.connect(resetDraw)
        self._mysql.signals.getPageSplited.connect(addLegend)        

        # self.needUpdataChart.connect(self._drawall)

        # self.signals.moveCompleted.connect(lambda : self._ui_lbl_startdate.setEnabled(True))
        # self.signals.moveCompleted.connect(lambda : self._ui_lbl_stopdate.setEnabled(True))

    def setUi(self):
        self.setWindowTitle("DB Visual Tools")
        self.setGeometry(0, 0, 1024, 768)
        self._ui_main_layout = QVBoxLayout()

        self._ui_topbox_layout = QVBoxLayout()
        self._ui_topbox_layout_line1 = FlowLayout()
        self._ui_pb_mysql_connect = QPushButton(self.tr("Connect"))
        self._ui_pb_mysql_connect.setCheckable(True)
        self._ui_pb_mysql_connect.clicked[bool].connect(lambda x: \
            {self._ui_pb_mysql_connect.setText(self.tr("Disconnect"))} if(x) \
            else {self._ui_pb_mysql_connect.setText(self.tr("Connect"))})
        self._ui_le_mysql_host = QLineEdit()
        self._ui_le_mysql_host.setPlaceholderText("127.0.0.1")
        self._ui_le_mysql_port = QLineEdit()
        self._ui_le_mysql_port.setPlaceholderText("3306")
        self._ui_le_mysql_user = QLineEdit()
        self._ui_le_mysql_user.setPlaceholderText(self.tr("Username"))
        self._ui_le_mysql_psw = QLineEdit()
        self._ui_le_mysql_psw.setPlaceholderText(self.tr("Password"))
        self._ui_le_mysql_dbname = QLineEdit()
        self._ui_le_mysql_dbname.setPlaceholderText(self.tr("DB Name"))
        self._ui_le_mysql_tbname = QLineEdit()
        self._ui_le_mysql_tbname.setPlaceholderText(self.tr("Table Name"))

        self._ui_topbox_layout_line1.addWidget(self._ui_pb_mysql_connect)
        self._ui_topbox_layout_line1.addWidget(self._ui_le_mysql_host)
        self._ui_topbox_layout_line1.addWidget(self._ui_le_mysql_port)
        self._ui_topbox_layout_line1.addWidget(self._ui_le_mysql_user)
        self._ui_topbox_layout_line1.addWidget(self._ui_le_mysql_psw)
        self._ui_topbox_layout_line1.addWidget(self._ui_le_mysql_dbname)
        self._ui_topbox_layout_line1.addWidget(self._ui_le_mysql_tbname)
        # self._ui_topbox_layout_line1.addStretch(5)

        # self._ui_topbox_layout_line2 = QHBoxLayout()
        self._ui_topbox_layout_line2_y = QHBoxLayout()
        self._ui_le_mysql_colname = QLineEdit()
        self._ui_le_mysql_colname.setPlaceholderText(self.tr("['Column', 'Name']"))
        self._ui_topbox_layout_line2_y.addWidget(self._ui_le_mysql_colname)
        self._ui_topbox_layout_line2_y.addStretch(5)
        self._ui_topbox_groupbox_line2_y = QGroupBox()
        self._ui_topbox_groupbox_line2_y.setTitle(self.tr("Y"))
        self._ui_topbox_groupbox_line2_y.setLayout(self._ui_topbox_layout_line2_y)

        self._ui_topbox_layout_line2_x = QHBoxLayout()
        self._ui_ckb_mysql_use_x = QCheckBox(self.tr("Use count"))
        self._ui_le_mysql_colname_x = QLineEdit()
        self._ui_le_mysql_colname_x.setPlaceholderText(self.tr("['Column', 'Name']"))
        self._ui_le_mysql_colname_x.setEnabled(False)
        self._ui_topbox_layout_line2_x.addWidget(self._ui_ckb_mysql_use_x)
        self._ui_topbox_layout_line2_x.addWidget(self._ui_le_mysql_colname_x)
        self._ui_topbox_layout_line2_x.addStretch(5)
        self._ui_topbox_groupbox_line2_x = QGroupBox()
        self._ui_topbox_groupbox_line2_x.setTitle(self.tr("X"))
        self._ui_topbox_groupbox_line2_x.setLayout(self._ui_topbox_layout_line2_x)

        self._ui_topbox_layout_line1.addWidget(self._ui_topbox_groupbox_line2_y)
        self._ui_topbox_layout_line1.addWidget(self._ui_topbox_groupbox_line2_x)

        self._ui_topbox_layout.addLayout(self._ui_topbox_layout_line1)
        # self._ui_topbox_layout.addLayout(self._ui_topbox_layout_line2)

        self._ui_topbox_group = QGroupBox()
        self._ui_topbox_group.setTitle("DB Connect")
        self._ui_topbox_group.setLayout(self._ui_topbox_layout)

        self._ui_topbox2_layout = QVBoxLayout()
        self._ui_topbox2_layout_line1 = FlowLayout()
        # self._ui_topbox2_layout_line2 = QHBoxLayout()
        self._ui_pb_move_pre = QPushButton(self.tr("Move Previous"))
        self._ui_pb_move_pre.setShortcut(QKeySequence(QKeySequence.MoveToPreviousPage))
        self._ui_pb_move_next = QPushButton(self.tr("Move Next"))
        self._ui_pb_move_next.setShortcut(QKeySequence(QKeySequence.MoveToNextPage))
        self._ui_ckb_move_type = QCheckBox(self.tr("Count"))
        self._ui_spb_move_count = QSpinBox()
        self._ui_spb_move_count.setPrefix(self.tr("Move:"))
        self._ui_spb_move_count.setRange(0, constant.INT_MAX)
        self._ui_spb_current_pos = QSpinBox()
        self._ui_spb_current_pos.setPrefix(self.tr("Current Pos:"))
        self._ui_spb_current_pos.setRange(0, constant.INT_MAX) # int max
        self._ui_ckb_level_type = QCheckBox(self.tr("gt"))
        self._ui_ckb_level_type.setVisible(False)
        self._ui_spb_level = QSpinBox()
        self._ui_spb_level.setRange(0, constant.INT_MAX)
        self._ui_spb_level.setValue(0)
        self._ui_spb_level.setVisible(False)
        self._ui_spb_chart_x_max = QSpinBox()
        self._ui_spb_chart_x_max.setPrefix(self.tr("X Max:"))
        self._ui_spb_chart_x_max.setRange(64, 1024*1024*1024)
        self._ui_spb_chart_x_max.setValue(1024)
        self._ui_spb_chart_x_max.setSingleStep(1024)
        self._ui_spb_fetch_split = QSpinBox()
        self._ui_spb_fetch_split.setPrefix(self.tr("Fetch Split:"))
        self._ui_spb_fetch_split.setRange(16, 20480)
        self._ui_spb_fetch_split.setValue(512)
        self._ui_spb_fetch_split.setSingleStep(16)
        self._ui_spb_fetch_split.valueChanged.connect(lambda x: self._ui_spb_chart_x_max.setSingleStep(x))
        self._ui_le_filter = QLineEdit()
        self._ui_le_filter.setPlaceholderText(self.tr("Colname1='test1' and Colname2='test2'"))
        self._ui_tbt_export = QToolButton()
        self._ui_tbt_export.setText(self.tr("Export CSV"))
        self._ui_tbt_saveimage = QToolButton()
        self._ui_tbt_saveimage.setText(self.tr("Save Image"))
        self._ui_tbt_printWindow = QToolButton()
        self._ui_tbt_printWindow.setText(self.tr("Print Window"))

        self._ui_topbox2_layout_line1.addWidget(self._ui_pb_move_pre)
        self._ui_topbox2_layout_line1.addWidget(self._ui_pb_move_next)
        self._ui_topbox2_layout_line1.addWidget(self._ui_ckb_move_type)
        self._ui_topbox2_layout_line1.addWidget(self._ui_spb_move_count)
        self._ui_topbox2_layout_line1.addWidget(self._ui_ckb_level_type)
        self._ui_topbox2_layout_line1.addWidget(self._ui_spb_level)
        self._ui_topbox2_layout_line1.addWidget(self._ui_spb_current_pos)
        # self._ui_topbox2_layout_line1.addStretch(5)
        # self._ui_topbox2_layout_line1.addSpacing(5)
        self._ui_topbox2_layout_line1.addWidget(self._ui_spb_chart_x_max)
        self._ui_topbox2_layout_line1.addWidget(self._ui_spb_fetch_split)
        self._ui_topbox2_layout_line1.addWidget(self._ui_le_filter)
        self._ui_topbox2_layout_line1.addWidget(self._ui_tbt_export)
        self._ui_topbox2_layout_line1.addWidget(self._ui_tbt_saveimage)
        self._ui_topbox2_layout_line1.addWidget(self._ui_tbt_printWindow)
        # self._ui_topbox2_layout_line1.addStretch(5)

        self._ui_topbox2_layout.addLayout(self._ui_topbox2_layout_line1)
        # self._ui_topbox2_layout.addLayout(self._ui_topbox2_layout_line2)

        self._ui_topbox2_group = QGroupBox()
        self._ui_topbox2_group.setTitle("Toolbox")
        self._ui_topbox2_group.setEnabled(False)
        self._ui_topbox2_group.setLayout(self._ui_topbox2_layout)

        # self._ui_chart = QChart()
        self._ui_chart_view = FigureCanvas(Figure(figsize=(10.2, 8)))
        # self._dynamic_ax = self._ui_chart_view.figure.subplots()
        self._mysql_col_color = ['tab:red', 'tab:blue', 'tab:brown', \
            'tab:green', 'tab:orange', 'tab:purple']
        # self._ui_chart.legend().setVisible(True)
        # self._ui_chart_series = []
        self._ui_chart_axis = []
        # self._ui_chart_view = QChartView()
        # self._ui_chart_view.setChart(self._ui_chart)

        self._ui_datarange_layout = QHBoxLayout()
        self._ui_lbl_startdate = QLabel(self.tr("Start Time"))
        # self._ui_lbl_startdate.setEnabled(False)
        self._ui_lbl_stopdate = QLabel(self.tr("Stop Time"))
        # self._ui_lbl_stopdate.setEnabled(False)

        self._ui_datarange_layout.addWidget(self._ui_lbl_startdate)
        self._ui_datarange_layout.addWidget(self._ui_lbl_stopdate)

        self._ui_main_layout.addWidget(self._ui_topbox_group)
        self._ui_main_layout.addWidget(self._ui_topbox2_group)
        self._ui_main_layout.addWidget(self._ui_chart_view)
        self._ui_main_layout.addLayout(self._ui_datarange_layout)

        self.setLayout(self._ui_main_layout)

    def configUi(self):
        self._ui_settings_path = "ui.ini"
        self._ui_settings_abs_path = os.path.join(os.getcwd(), self._ui_settings_path)
        self._ui_settings = QSettings(self._ui_settings_abs_path, QSettings.IniFormat)
        # self._ui_settings.setValue("test", "test")
        self._ui_le_mysql_host.setText(( \
            lambda: "127.0.0.1" \
            if not self._ui_settings.value("input/host") \
                else self._ui_settings.value("input/host"))())
        self._ui_le_mysql_host.textChanged.connect( \
            lambda x: self._ui_settings.setValue("input/host", x))
        self._ui_le_mysql_port.setText(( \
            lambda: "3306" \
            if not self._ui_settings.value("input/port") \
                else self._ui_settings.value("input/port"))())
        self._ui_le_mysql_port.textChanged.connect( \
            lambda x: self._ui_settings.setValue("input/port", x))
        self._ui_le_mysql_user.setText(( \
            lambda: "root" \
            if not self._ui_settings.value("input/user") \
                else self._ui_settings.value("input/user"))())
        self._ui_le_mysql_user.textChanged.connect( \
            lambda x: self._ui_settings.setValue("input/user", x))
        self._ui_le_mysql_psw.setText(( \
            lambda: "root" \
            if not self._ui_settings.value("input/psw") \
                else self._ui_settings.value("input/psw"))())
        self._ui_le_mysql_psw.textChanged.connect( \
            lambda x: self._ui_settings.setValue("input/psw", x))
        self._ui_le_mysql_dbname.setText(( \
            lambda: "" \
            if not self._ui_settings.value("input/dbname") \
                else self._ui_settings.value("input/dbname"))())
        self._ui_le_mysql_dbname.textChanged.connect( \
            lambda x: self._ui_settings.setValue("input/dbname", x))
        self._ui_le_mysql_tbname.setText(( \
            lambda: "" \
            if not self._ui_settings.value("input/tblname") \
                else self._ui_settings.value("input/tblname"))())
        self._ui_le_mysql_tbname.textChanged.connect( \
            lambda x: self._ui_settings.setValue("input/tblname", x))
        self._ui_le_mysql_colname.setText(( \
            lambda: "" \
            if not self._ui_settings.value("input/colname") \
                else self._ui_settings.value("input/colname"))())
        self._ui_le_mysql_colname.textChanged.connect( \
            lambda x: self._ui_settings.setValue("input/colname", x))
        self._ui_ckb_mysql_use_x.clicked[bool].connect( \
            lambda x: self._ui_ckb_mysql_use_x.setText(self.tr("From DB")) \
                if (x) else self._ui_ckb_mysql_use_x.setText(self.tr("Count")))
        self._ui_ckb_mysql_use_x.clicked[bool].connect( \
            lambda x: self._ui_le_mysql_colname_x.setEnabled(True) \
                if (x) else self._ui_le_mysql_colname_x.setEnabled(False))
        self._ui_le_mysql_colname_x.setText(( \
            lambda: "" \
            if not self._ui_settings.value("input/colname_x") \
                else self._ui_settings.value("input/colname_x"))())
        self._ui_le_mysql_colname_x.textChanged.connect( \
            lambda x: self._ui_settings.setValue("input/colname_x", x))                                                 
        self._ui_spb_move_count.setValue(( \
            lambda: 10 \
            if not self._ui_settings.value("input/move_count") \
                else int(self._ui_settings.value("input/move_count")))())
        self._ui_spb_move_count.valueChanged.connect( \
            lambda x: self._ui_settings.setValue("input/move_count", x))
        self._ui_spb_level.setValue(( \
            lambda: 10 \
            if not self._ui_settings.value("input/level") \
                else int(self._ui_settings.value("input/level")))())
        self._ui_spb_level.valueChanged.connect( \
            lambda x: self._ui_settings.setValue("input/level", x))
        self._ui_spb_current_pos.setValue(( \
            lambda: 0 \
            if not self._ui_settings.value("input/current_pos") \
                else int(self._ui_settings.value("input/current_pos")))())
        self._ui_spb_current_pos.valueChanged.connect( \
            lambda x: self._ui_settings.setValue("input/current_pos", x))
        self._ui_spb_chart_x_max.setValue(( \
            lambda: 10 \
            if not self._ui_settings.value("input/chart_x_max") \
                else int(self._ui_settings.value("input/chart_x_max")))())
        self._ui_spb_chart_x_max.valueChanged.connect( \
            lambda x: self._ui_settings.setValue("input/chart_x_max", x))
        self._ui_le_filter.setText(( \
            lambda: "" \
            if not self._ui_settings.value("input/filterstr") \
                else self._ui_settings.value("input/filterstr"))())
        self._ui_le_filter.textChanged.connect( \
            lambda x: self._ui_settings.setValue("input/filterstr", x))       

        self._ui_pb_mysql_connect.clicked.connect(lambda :self._ui_pb_mysql_connect.setDisabled(True))
        self._ui_pb_mysql_connect.clicked[bool].connect(self._ui_topbox2_group.setEnabled)

        self._ui_ckb_move_type.clicked[bool].connect( \
            lambda x: self._ui_ckb_move_type.setText(self.tr("Level")) \
                if (x) else self._ui_ckb_move_type.setText(self.tr("count")))
        self._ui_ckb_move_type.clicked[bool].connect( \
            lambda x: self._ui_spb_move_count.setVisible(False) \
                if (x) else self._ui_spb_move_count.setVisible(True))
        self._ui_ckb_move_type.clicked[bool].connect( \
            lambda x: self._ui_ckb_level_type.setVisible(True) \
                if(x) else self._ui_ckb_level_type.setVisible(False))
        self._ui_ckb_move_type.clicked[bool].connect( \
            lambda x: self._ui_spb_level.setVisible(True) \
                if(x) else self._ui_spb_level.setVisible(False))
        self._ui_ckb_level_type.clicked[bool].connect( \
            lambda x: self._ui_ckb_level_type.setText(self.tr("lt"))\
                if(x) else self._ui_ckb_level_type.setText(self.tr("gt")))

        # self._ui_pb_move_pre.clicked.connect( \
        #     lambda: self._ui_spb_current_pos.setValue(
        #         self._ui_spb_current_pos.value()-self._ui_spb_move_count.value() \
        #     ) if(self._ui_spb_current_pos.value()-self._ui_spb_move_count.value()>0) \
        #         else self._ui_spb_current_pos.setValue(0))
        # self._ui_pb_move_next.clicked.connect( \
        #     lambda: self._ui_spb_current_pos.setValue(
        #         self._ui_spb_current_pos.value()+self._ui_spb_move_count.value() \
        #     ) if(self._ui_spb_current_pos.value()+self._ui_spb_move_count.value()< \
        #     constant.INT_MAX) \
        #         else self._ui_spb_current_pos.setValue(constant.INT_MAX-self._ui_spb_chart_x_max.value()))

    def _connect(self, b):
        if (b):
            self._ui_le_mysql_host.setEnabled(False)
            self._ui_le_mysql_port.setEnabled(False)
            self._ui_le_mysql_user.setEnabled(False)
            self._ui_le_mysql_psw.setEnabled(False)
            self._mysql.connect( { \
                "host":self._ui_le_mysql_host.text(), \
                "port":self._ui_le_mysql_port.text(),
                "user": self._ui_le_mysql_user.text(),
                "password": self._ui_le_mysql_psw.text(),
                "database":self._ui_le_mysql_dbname.text(),
                })
            self.signals.connected.emit()
        else:
            # self.__cursor.close()
            # self.__mysql_conn.close()
            self._mysql.disconnect()
            self._ui_le_mysql_host.setEnabled(True)
            self._ui_le_mysql_port.setEnabled(True)
            self._ui_le_mysql_user.setEnabled(True)
            self._ui_le_mysql_psw.setEnabled(True)
            self.signals.disconnected.emit()

    def print_window(self):
        fn = datetime.datetime.now().strftime("%Y-%m-%d-%H-%M-%S.png")
        fd = QFileDialog.getSaveFileName(self, self.tr("Save as png"), fn, self.tr("png (*.png)"))
        rect = self.geometry()
        p = self.grab(rect)
        p.save(fd[0], "png")

    def save_image(self):
        fn = datetime.datetime.now().strftime("%Y-%m-%d-%H-%M-%S.png")
        fd = QFileDialog.getSaveFileName(self, self.tr("Save as png"), fn, self.tr("png (*.png)"))
        self._ui_chart_view.figure.savefig(fd[0])

    def save_current(self):
        fn = datetime.datetime.now().strftime("%Y-%m-%d-%H-%M-%S.csv")
        fd = QFileDialog.getSaveFileName(self, self.tr("Save as csv"), fn, self.tr("CSV (*.csv)"))
        self._mysql.signals.executedSQLFinished.connect( \
            lambda x:self.save_current_title({"path":fd[0], "data":x}))
        self._mysql.executeSQL("SHOW COLUMNS FROM " + self._ui_le_mysql_tbname.text())

    @Slot(object)
    def save_current_title(self, d):
        # print (d)
        with open(d['path'], 'w', newline='') as csvfile:
            w = csv.writer(csvfile, delimiter=',')
            w.writerow(list(map(lambda c: c[0], d["data"])))
        self._mysql.signals.executedSQLFinished.disconnect()

        self._mysql.signals.executedSQLFinished.connect( \
            lambda x:self.save_current_data({"path":d["path"], "data":x}))
        filterstr = ""
        if len(self._ui_le_filter.text()):
            filterstr = " WHERE " + self._ui_le_filter.text()
        self._mysql.executeSQL("SELECT * FROM " \
            + self._ui_le_mysql_tbname.text() \
                + filterstr \
                + " LIMIT " + str(self._ui_spb_chart_x_max.value()) \
                    + " OFFSET " + str(self._ui_spb_current_pos.value()))
    @Slot(object)
    def save_current_data(self, d):
        with open(d['path'], 'a', newline='') as csvfile:
            w = csv.writer(csvfile, delimiter=',')
            w.writerows(d["data"])
        self._mysql.signals.executedSQLFinished.disconnect()
        self.setEnabled(True)

    def get_data(self):
        # cn = self._mysql_colnames_y + self._mysql_colnames_x
        query_para = { \
            "tblname": self._ui_le_mysql_tbname.text(),
            "colnames": self._mysql_colnames,
            "offset": self._ui_spb_current_pos.value(),
            "limit": self._ui_spb_chart_x_max.value(),
            "pagelimited": self._ui_spb_fetch_split.value(),
            "filter": self._ui_le_filter.text()
            }
        # print(query_para)
        self._mysql.getPage(query_para)

    # def _append_series(self, s):
    #     self._ui_chart_series.append(s)
    #     self._ui_chart.addSeries(s)

    # def _pop_series(self):
    #     s = self._ui_chart_series.pop(0)
    #     self._ui_chart.removeSeries(s)
    #     return s

    # def _clearall_series(self):
    #     for s in self._ui_chart.series():
    #         s.clear()

    # def _removeall_serires(self):
    #     self._ui_chart.removeAllSeries()
    #     self._ui_chart_series = []

    # def _append_axis(self, axis, s):
    #     self._ui_chart_axis.append(axis)
    #     self._ui_chart.setAxisX(axis["x"], s)
    #     self._ui_chart.setAxisY(axis["y"], s)

    # def _pop_axis(self):
    #     a = self._ui_chart_axis.pop(0)
    #     self._ui_chart.removeAxis(a["x"])
    #     self._ui_chart.removeAxis(a["y"])
    #     return a

    # def _removeall_axis(self):
    #     for s in self._ui_chart_series:
    #         x = self._ui_chart.axisX(s)
    #         self._ui_chart.removeAxis(x)
    #         y = self._ui_chart.axisY(s)
    #         self._ui_chart.removeAxis(y)
    #     self._ui_chart_axis = []

    def _build_charts_axis(self):
        if (len(self._ui_chart_axis)<len(self._mysql_colnames_y)):
            self._ui_chart_axis.append(self._ui_chart_view.figure.subplots())
            for i in range(len(self._ui_chart_axis), len(self._mysql_colnames_y)):
                self._ui_chart_axis.append(self._ui_chart_axis[0].twinx())
        self._ui_chart_axis[0].set_xlabel('Count')
        for i in range(len(self._ui_chart_axis)):
            self._ui_chart_axis[i].set_ylabel(self._mysql_colnames_y[i])
        # self._ui_chart_view.figure.legend(loc=1, bbox_to_anchor=(1,1), bbox_transform=self._ui_chart_axis[0].transAxes)

    def _clear_charts_axis(self):
        for axis in self._ui_chart_axis:
            axis.clear()
        
    def _drawall(self, info):
        data = info["data"]
        colynums = len(self._mysql_colnames_y)
        lns = []
        for i in range(colynums):
            startpos = info["index"]*info["split"]
            if self._ui_ckb_mysql_use_x.isChecked():
                if (not self._ui_lbl_startdate.isEnabled()):
                    self._ui_lbl_startdate.setEnabled(True)
                    self._ui_lbl_startdate.setText(str(data[0][colynums]))
                self._ui_lbl_stopdate.setText(str(data[-1][colynums]))
            # p = map(QPoint, range(startpos, len(data)+startpos), [c[i] for c in data ])
            # l = list(p)
            # l1 = map(lambda c: c.y(), l)
            # l2 = list(l1)
            # _min = min(l2)
            # if _min < self._ui_chart.axisY(self._ui_chart_series[i]).min():
            #     self._ui_chart.axisY(self._ui_chart_series[i]).setMin(_min)
            # _max = max(l2)
            # if _max > self._ui_chart.axisY(self._ui_chart_series[i]).max():
            #     self._ui_chart.axisY(self._ui_chart_series[i]).setMax(_max)
            # # self._ui_chart.axisY(self._ui_chart_series[i]).setRange(_min, _max)
            # pts = self._ui_chart_series[i].pointsVector()
            # pts.extend(l)
            # self._ui_chart_series[i].replace(pts)
            lns += self._ui_chart_axis[i].plot(range(startpos, len(data)+startpos), [c[i] for c in data], color=self._mysql_col_color[i], label=self._mysql_colnames[i])
        for i in range(colynums):
            self._ui_chart_axis[i].figure.canvas.draw()
        # # self._ui_chart_axis[0].plot(range(startpos, len(data)+startpos), [c[0] for c in data], color="tab:red")
        # # self._ui_chart_axis[0].figure.canvas.draw()
        # # self._ui_chart_axis[1].plot(range(startpos, len(data)+startpos), [c[1] for c in data], color="tab:blue")
        # # self._ui_chart_axis[1].figure.canvas.draw()
        # labs = [l.get_label() for l in lns]
        # self._ui_chart_axis[0].legend(lns, labs, loc=0)        

    def display_init(self):
        self._clear_charts_axis()
        self._build_charts_axis()

        # pass
        # colnames = eval(self._ui_le_mysql_colname.text())
        # axisnum = len(colnames)
        # self._removeall_axis()
        # self._removeall_serires()
        # x = QValueAxis()
        # x.setTitleText("Count")
        # x.setRange(0, self._ui_spb_chart_x_max.value())
        # for i in range(axisnum):
        #     s = QLineSeries()
        #     s.setName(colnames[i])
        #     self._append_series(s)
        #     y = QValueAxis()
        #     y.setTitleText(colnames[i])
        #     self._append_axis({"x":x, "y":y}, s) 

        

    def display_page(self, data):
        self._drawall(data)
        self.setEnabled(True)
        self.signals.moveCompleted.emit()

    def get_data_pre(self, data):
        # print (data)
        self._mysql.signals.executedSQLFinished.disconnect()
        if (len(data)):
            self._ui_spb_current_pos.setValue(data[0][0])
            self.get_data()
        else:
            self.setEnabled(True)
            self.signals.moveCompleted.emit()

    def _move_pre(self):
        if self._ui_ckb_move_type.isChecked():
            self._mysql.signals.executedSQLFinished.connect( \
                        lambda x:self.get_data_pre(x))
            colnames = eval(self._ui_le_mysql_colname.text())
            self._mysql.executeSQL( \
                "select rownum from " + \
                    "(select @rownum:=@rownum+1 as rownum, " +  \
                        self._ui_le_mysql_tbname.text() + ".* from " + \
                        "(select @rownum:=0) a, " + self._ui_le_mysql_tbname.text() + \
                    ") c where " + colnames[0] + (lambda: " < " \
                                                    if self._ui_ckb_level_type.isChecked() \
                                                        else " > ")() + \
                        str(self._ui_spb_level.value()) + \
                    " and rownum < " + str(self._ui_spb_current_pos.value()) + \
                    " order by rownum desc")

            # s = "select rownum from (select @rownum:=@rownum+1 as rownum, `data-2019-11-28`.ch1v from (select @rownum:=0) a, `data-2019-11-28`) c where ch1v >18 limit 2000"
            # self._mysql.executeSQL(s)
        else:
            ( \
            lambda: self._ui_spb_current_pos.setValue(
                self._ui_spb_current_pos.value()-self._ui_spb_move_count.value() \
            ) if(self._ui_spb_current_pos.value()-self._ui_spb_move_count.value()>0) \
                else self._ui_spb_current_pos.setValue(0))()
            self.get_data()        

    def _move_next(self):
        if self._ui_ckb_move_type.isChecked():
            self._mysql.signals.executedSQLFinished.connect( \
                        lambda x:self.get_data_pre(x))
            colnames = eval(self._ui_le_mysql_colname.text())
            self._mysql.executeSQL( \
                "select rownum from " + \
                    "(select @rownum:=@rownum+1 as rownum, " +  \
                        self._ui_le_mysql_tbname.text() + ".* from " + \
                        "(select @rownum:=0) a, " + self._ui_le_mysql_tbname.text() + \
                    ") c where " + colnames[0] + (lambda: " < " \
                                                    if self._ui_ckb_level_type.isChecked() \
                                                        else " > ")() + \
                        str(self._ui_spb_level.value()) + \
                    " and rownum > " + str(self._ui_spb_current_pos.value()) + \
                    " order by rownum asc")
        else:
            (lambda: self._ui_spb_current_pos.setValue(
                self._ui_spb_current_pos.value()+self._ui_spb_move_count.value() \
            ) if(self._ui_spb_current_pos.value()+self._ui_spb_move_count.value()< \
            constant.INT_MAX) \
                else self._ui_spb_current_pos.setValue(constant.INT_MAX-self._ui_spb_chart_x_max.value()))()
            self.get_data()


class MainWindow(QMainWindow):
    def __init__(self):
        super(MainWindow, self).__init__()
        self.setWindowTitle(self.tr("Visaul Chart For MySQL"))
        self._widget = MainWidget(self)
        self.setCentralWidget(self._widget)

        helpMenu = self.menuBar().addMenu(self.tr("&Help"))
        manualAct = QAction(self.tr("&Manal"), self)
        manualAct.triggered.connect(lambda : \
            os.startfile(os.path.join("doc", "manual.rtf")))
        aboutAct = QAction(self.tr("&About"), self)
        aboutAct.triggered.connect(lambda : \
            QMessageBox.information(None, "About", "Copy Right @ YSAIOT", QMessageBox.Yes))
        helpMenu.addAction(manualAct)
        helpMenu.addAction(aboutAct)

        self._widget.signals.connected.connect(lambda : \
            self.statusBar().showMessage(self.tr("Connected")))
        self._widget.signals.disconnected.connect(lambda : \
            self.statusBar().showMessage(self.tr("Disconnected")))
        
        ww = WWaiting()
        ww.signals.timeout.connect(lambda: self._widget.setEnabled(True))
        self._widget.signals.moveStarted.connect(ww.start)
        self._widget.signals.moveCompleted.connect(ww.stop)
        self.statusBar().addPermanentWidget(ww)

if __name__=="__main__":
    a = QApplication()
    w = MainWindow()
    w.show()
    sys.exit(a.exec_())